<?php
session_cache_expire(999999999);
if(!isset($_SESSION)) 
{ 
	session_start(); 
}
	
	$lang = $_SESSION['lang'];
	if (empty($lang) || $lang == "EN") {
		include "../value/value_en.php";
	} else if($lang == "VN") {
		include "../value/value_vn.php";
	}
	else {
		include "../value/value_kr.php";
	}
include '../operations/connection.php';
	$id=$_REQUEST['id'];
	$id_employee=$_SESSION['IDEmployee'];
	$queryListErrorByIDPr = mysql_query("SELECT tp.IDTest, tp.Title, tp.TextTest, tp.Time, tp.`Status`,e.FullName, tp.`Correct`,
		tp.IDTestTemp,pp.IDView,pp.NameProject,pc.backgroundColor,pc.backgroundConfirm,tp.Comment_Last,tp.NameEmployee_Last 
	   FROM pr_testproject tp, employee e ,pr_project pp,priority_color pc  WHERE pp.IDProject = tp.IDProject AND tp.IDProject = '$id' AND e.IDEmployee = tp.IDEmployee 
	    AND pc.id = tp.IdPriorityColor  ORDER BY tp.`Status`=2 ASC,tp.DateConfirm DESC,tp.IdPriorityColor DESC,tp.Time DESC; ");
	
	
	$querydetail=mysql_query("SELECT p.NameProject, p.Description,DATE_FORMAT( p.DateStart, '%Y-%m-%d') as DateStart, p.DateComplete, p.IDManager,p.IDView, p.Status
	FROM pr_project p,pr_composition c, employee e WHERE ((c.IDEmployee = '$id_employee'  AND e.IDEmployee = c.IDEmployee )
	 or (p.IDManager = '$id_employee'  AND e.IDEmployee = p.IDManager )) AND p.IDProject = c.IDProject AND  p.IDProject ='$id'
	 GROUP BY p.IDProject ORDER BY p.NameProject ASC 
	");


	
	//include '../Classes/PHPExcel.php';
	/** Include path **/
	set_include_path(get_include_path() . PATH_SEPARATOR . 'ClassesExcel/');
	
	/** PHPExcel */
	require_once 'PHPExcel.php';
	
	/** PHPExcel_RichText */
	require_once 'PHPExcel/RichText.php';
	
	
	$filename ="export_project.xls";
	
	$objPHPExcel = new PHPExcel();

	
	$styleArray = array(
		'font' => array(
			'bold' => true,
			'size' => 11,
		),
		'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
			'wrap' => true,
		),
		'borders' => array(
			'top' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'bottom' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'left' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'right' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
		),
		'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
			'rotation' => 90,
			'startcolor' => array(
				'argb' => 'FFA0A0A0',
			),
			'endcolor' => array(
				'argb' => 'FFFFFFFF',
			),
		),
	);
	
	$styleArray2 = array(
			'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
			'wrap' => true,
		),
		'borders' => array(
			'top' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'bottom' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'left' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'right' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
		),
		
	);
	$styleArray3 = array(
			'alignment' => array(
			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
			'wrap' => true,
		),
		'borders' => array(
			'top' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'bottom' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'left' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
			'right' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
			),
		),
		
	);
	
	
	
	$objPHPExcel->setActiveSheetIndex(0);
	while ($row=mysql_fetch_array($querydetail)){ 
		$idmanager = $row[IDManager];

		$queryManger =mysql_query("SELECT emp.FullName FROM employee emp WHERE emp.IDEmployee = '$idmanager'" );
		
		$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Times New Roman');
		$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
		$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$l_project." : ".$row['NameProject']);
		
		$filename ="export_project_error_".$row['NameProject'].".xls";
		
		$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25);
		$objPHPExcel->getActiveSheet()->mergeCells('A2:C2');
		$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray3);
		$objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray($styleArray3);
		$objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray($styleArray3);
		$objPHPExcel->getActiveSheet()->setCellValue('A2',$l_start_date." : ".$row['DateStart']);
	
		while ($r = mysql_fetch_array($queryManger)){
			$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(25);
			$objPHPExcel->getActiveSheet()->mergeCells('A3:C3');
			$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleArray3);
			$objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($styleArray3);
			$objPHPExcel->getActiveSheet()->getStyle('C3')->applyFromArray($styleArray3);
			$objPHPExcel->getActiveSheet()->setCellValue('A3',$l_manager." : ".$r['FullName']);
		}
		
		$status="";
		if($row['Status']==0){
			$status= $l_status_project_no;
		}else{
			$status= $l_status_project_done;
		}
		$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(25);
		$objPHPExcel->getActiveSheet()->mergeCells('A4:C4');
		$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleArray3);
		$objPHPExcel->getActiveSheet()->getStyle('B4')->applyFromArray($styleArray3);
		$objPHPExcel->getActiveSheet()->getStyle('C4')->applyFromArray($styleArray3);
		$objPHPExcel->getActiveSheet()->setCellValue('A4',$l_status." : ".$status);
	
	}
	
	
	$objPHPExcel->getActiveSheet()->freezePane('A6');
	
	$objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(25);
	$objPHPExcel->getActiveSheet()->getStyle('A5')->applyFromArray($styleArray);
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
	$objPHPExcel->getActiveSheet()->setCellValue('A5',$l_no);
	
	$objPHPExcel->getActiveSheet()->getStyle('B5')->applyFromArray($styleArray);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	$objPHPExcel->getActiveSheet()->setCellValue('B5', $l_code);
	
	$objPHPExcel->getActiveSheet()->getStyle('C5')->applyFromArray($styleArray);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
	$objPHPExcel->getActiveSheet()->setCellValue('C5', $l_status);
	
	$objPHPExcel->getActiveSheet()->getStyle('D5')->applyFromArray($styleArray);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
	$objPHPExcel->getActiveSheet()->setCellValue('D5', $l_title);
	
	
	$objPHPExcel->getActiveSheet()->getStyle('E5')->applyFromArray($styleArray);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->setCellValue('E5', $l_create_by);
	
	$objPHPExcel->getActiveSheet()->getStyle('F5')->applyFromArray($styleArray);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
	$objPHPExcel->getActiveSheet()->setCellValue('F5', $l_created_date);
	$key=6;
	if(mysql_num_rows($queryListErrorByIDPr)>0){
			$vitri=1;
			while ($row=mysql_fetch_array($queryListErrorByIDPr)){
				try{
					//if($vitri>=1460){
				$idtestproject = $row['IDTest'];
				$titletestpro = $row['Title'];
				$contenttestpro = $row['TextTest'];
				$fullnames = $row['FullName'];
				$time = $row['Time'];
				$indexcomment = (($page-1)*20)+$i;
				
				$stringcolortest = substr($row['Title'],0,-14);
				$stringcolor = substr($row['Comment_Last'],0,-14);
				$correct=$row['Correct'];
				
				//tag html tranh be giao dien
				//$stringcolor = strip_tags($stringcolor);
				
				$idtesppr_temp = $row['IDTestTemp'];
				$idviewpro = $row['IDView'];
				$nampro = $row['NameProject'];
				
				$temp_mo = explode("-", $idtesppr_temp);
				$temp_mo_last = $temp_mo[0];
				$m="";
				if ($temp_mo_last==''){
					$m ="";
				}
				else if ($temp_mo_last==$nampro){
					$m = $idviewpro."-".$temp_mo[1];
				}else{
					$m = $idviewpro."-".$idtesppr_temp;
				}
				$bgcolor = $row['backgroundColor'];
				
				$status = $row['Status'];
				$temp_status ="";
				if ($status==2){
						$bgcolor = $row['backgroundConfirm'];
						if($correct==0)
						$bgcolor = "B0A5CA";
					}
				
				if ($status==0){
					$nameStatus=$l_no_fix;
				}else if ($status==1){
					$nameStatus=$l_fix_complete;
				}else if ($status==2){
					$nameStatus=$l_confirm_excel;
				}else if ($status==-1){
					if($correct==1)
						$nameStatus=$l_newerror;
					else
						$nameStatus=$l_incorrect;
				}
				$styleArray4 = array(
						'font' => array(
							//'bold' => true,
							'size' => 11,
						),
						'alignment' => array(
							'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
							'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
							'wrap' => true,
						),
						'borders' => array(
							'top' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
							'bottom' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
							'left' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
							'right' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
						),
						'fill' => array(
							'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
							'rotation' => 90,
							'startcolor' => array(
								'argb' => 'FF'.$bgcolor,
							),
							'endcolor' => array(
								'argb' => 'FF'.$bgcolor,
							),
						),
					);
				
				$styleArray5 = array(
						'font' => array(
							//'bold' => true,
							'size' => 11,
						),
						'alignment' => array(
							'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
							'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
							'wrap' => true,
						),
						'borders' => array(
							'top' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
							'bottom' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
							'left' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
							'right' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
							),
						),
						'fill' => array(
							'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
							'rotation' => 90,
							'startcolor' => array(
								'argb' => 'FF'.$bgcolor,
							),
							'endcolor' => array(
								'argb' => 'FF'.$bgcolor,
							),
						),
					);
				$objPHPExcel->getActiveSheet()->getRowDimension($key)->setRowHeight(25);
				$objPHPExcel->getActiveSheet()->getStyle('A'.$key)->applyFromArray($styleArray4);
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$key, $vitri);
				
				$objPHPExcel->getActiveSheet()->getStyle('B'.$key)->applyFromArray($styleArray4);
				$objPHPExcel->getActiveSheet()->setCellValue('B'.$key, $row['IDView'].'-'.$row['IDTestTemp']);
				
				$objPHPExcel->getActiveSheet()->getStyle('C'.$key)->applyFromArray($styleArray4);
				$objPHPExcel->getActiveSheet()->setCellValue('C'.$key,$nameStatus);
				
				if(strlen($stringcolortest)==0)
					$stringcolortest= $titletestpro ;

				$objPHPExcel->getActiveSheet()->getStyle('D'.$key)->applyFromArray($styleArray5);
				$objPHPExcel->getActiveSheet()->setCellValue('D'.$key, $stringcolortest);
				
				
				$objPHPExcel->getActiveSheet()->getStyle('E'.$key)->applyFromArray($styleArray4);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$key,$row['FullName']);
				
				$objPHPExcel->getActiveSheet()->getStyle('F'.$key)->applyFromArray($styleArray4);
				$objPHPExcel->getActiveSheet()->setCellValue('F'.$key, $row['Time']);
				$key++;
				//	}
				$vitri++;
		        
					
		        if($vitri==1460)
		        	break;
				}catch (exception  $e){
					 echo $e->getMessage();
				     echo $e->getCode(); // Lỗi do code
				     echo $e->getFile(); // Lỗi từ một tin tệp
				     echo $e->getLine(); // lấy lỗi tại một dòng lệnh
				     echo $e->getTraceAsString(); // biến lỗi thành dạng chuỗi
				}
			}
	}
	include 'PHPExcel/IOFactory.php';
	
	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
	header("Cache-Control: no-store, no-cache, must-revalidate");
	header("Cache-Control: post-check=0, pre-check=0", false);
	header("Pragma: no-cache");
	header("Content-type: application/vnd.ms-excel");
	header('Content-Disposition: attachment; filename=' . $filename);
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');

	mysql_close($connection);
exit;

?>

